package org.springblade.modules.util;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.CellType;
import org.springblade.modules.app.entity.AppExcel;
import org.springblade.modules.app.service.IAppExcelService;

import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;

/**
 *
 */
public class PoiUtil {

	/**
	 * 读取excel
	 */
	public static void readExcel(String path, IAppExcelService excelService) {
		try {

			InputStream inputStream = new FileInputStream(path);//创建一个输入流读取单元格
			POIFSFileSystem fileSystem = new POIFSFileSystem(inputStream);//包装类，将读取的内容放入内存中
			HSSFWorkbook wb = new HSSFWorkbook(fileSystem);
			HSSFSheet hssfSheet = wb.getSheetAt(0); // 获取第一个Sheet页
			//第一张表
			//刑事案件发案趋势图-森林公安
			JSONObject jsonObject = new JSONObject();
			JSONArray json = read1(hssfSheet, 2, 14);
			JSONArray json2 = read1(hssfSheet, 16, 28);
			jsonObject.put("table_1", json);//野生动物案件数据
			jsonObject.put("table_2", json2);//森林案件数据
			String result = jsonObject.toJSONString();
			AppExcel appExcel = new AppExcel();
			appExcel.setId(1);
			appExcel.setJson(result);
			excelService.updateById(appExcel);
			//第二张表
			//行政发案趋势图-森林公安
			hssfSheet = wb.getSheetAt(1); // 获取第二个Sheet页
			jsonObject = new JSONObject();
			json = read1(hssfSheet, 2, 14);
			json2 = read1(hssfSheet, 16, 28);
			jsonObject.put("table_1", json);//野生动物案件数据
			jsonObject.put("table_2", json2);//森林案件数据
			result = jsonObject.toJSONString();
			appExcel = new AppExcel();
			appExcel.setId(2);
			appExcel.setJson(result);
			excelService.updateById(appExcel);
			//第三张图
			//森林案件统计图（当月）-森林公安
			hssfSheet = wb.getSheetAt(2); // 获取第三个Sheet页
			json = read2(hssfSheet);
			result = json.toJSONString();
			appExcel = new AppExcel();
			appExcel.setId(3);
			appExcel.setJson(result);
			excelService.updateById(appExcel);
			//第四张图
			//野生动物案件统计图（当月）-森林公安
			hssfSheet = wb.getSheetAt(3); // 获取第三个Sheet页
			json = read3(hssfSheet);
			result = json.toJSONString();
			appExcel = new AppExcel();
			appExcel.setId(4);
			appExcel.setJson(result);
			excelService.updateById(appExcel);
			//第五张图
			//野生动物案件统计图（当月）-森林公安
			hssfSheet = wb.getSheetAt(4); // 获取第三个Sheet页
			json = read4(hssfSheet);
			result = json.toJSONString();
			appExcel = new AppExcel();
			appExcel.setId(5);
			appExcel.setJson(result);
			excelService.updateById(appExcel);
			//第六张图
			//野生动物案件统计图（当月）-森林公安
			hssfSheet = wb.getSheetAt(5); // 获取第三个Sheet页
			json = read5(hssfSheet);
			result = json.toJSONString();
			appExcel = new AppExcel();
			appExcel.setId(6);
			appExcel.setJson(result);
			excelService.updateById(appExcel);
			//第七张图
			//野生动物案件统计图（当月）-森林公安
			hssfSheet = wb.getSheetAt(6); // 获取第三个Sheet页
			json = read6(hssfSheet);
			result = json.toJSONString();
			appExcel = new AppExcel();
			appExcel.setId(7);
			appExcel.setJson(result);
			excelService.updateById(appExcel);
			//第八张图
			//案件分区统计图（当月）-森林公安
			hssfSheet = wb.getSheetAt(7); // 获取第三个Sheet页
			jsonObject = new JSONObject();
			json = read7(hssfSheet, 2, 7);
			json2 = read7(hssfSheet, 8, 13);
			jsonObject.put("table_1", json);//野生动物案件数据
			jsonObject.put("table_2", json2);//野生动物案件数据
			result = jsonObject.toJSONString();
			appExcel = new AppExcel();
			appExcel.setId(8);
			appExcel.setJson(result);
			excelService.updateById(appExcel);
			//第九张图
			//火情趋势统计图-防火预警
			hssfSheet = wb.getSheetAt(8); // 获取第一个Sheet页
			jsonObject = new JSONObject();
			json = read1(hssfSheet, 2, 14);
			json2 = read1(hssfSheet, 16, 28);
			jsonObject.put("table_1", json);//野生动物案件数据
			jsonObject.put("table_2", json2);//森林案件数据
			result = jsonObject.toJSONString();
			appExcel = new AppExcel();
			appExcel.setId(9);
			appExcel.setJson(result);
			excelService.updateById(appExcel);
			//第十张图
			//起火原因统计图（当月）-防火预警
			hssfSheet = wb.getSheetAt(9); // 获取第三个Sheet页
			json = read8(hssfSheet);
			result = json.toJSONString();
			appExcel = new AppExcel();
			appExcel.setId(10);
			appExcel.setJson(result);
			excelService.updateById(appExcel);
			//第十一张图
			//起火原因统计图（当月）-防火预警
			hssfSheet = wb.getSheetAt(10); // 获取第三个Sheet页
			json = read9(hssfSheet);
			result = json.toJSONString();
			appExcel = new AppExcel();
			appExcel.setId(11);
			appExcel.setJson(result);
			excelService.updateById(appExcel);
			//第十二张图
			//起火原因统计图（当月）-防火预警
			hssfSheet = wb.getSheetAt(11); // 获取第三个Sheet页
			json = read10(hssfSheet);
			result = json.toJSONString();
			appExcel = new AppExcel();
			appExcel.setId(12);
			appExcel.setJson(result);
			excelService.updateById(appExcel);
			//第十三张图
			//起火原因统计图（当月）-防火预警
			hssfSheet = wb.getSheetAt(12); // 获取第三个Sheet页
			json = read11(hssfSheet);
			result = json.toJSONString();
			appExcel = new AppExcel();
			appExcel.setId(13);
			appExcel.setJson(result);
			excelService.updateById(appExcel);
			//第十四张图
			//起火原因统计图（当月）-防火预警
			hssfSheet = wb.getSheetAt(13); // 获取第三个Sheet页
			json = read12(hssfSheet);
			result = json.toJSONString();
			appExcel = new AppExcel();
			appExcel.setId(14);
			appExcel.setJson(result);
			excelService.updateById(appExcel);
			//第十五张图
			//起火原因统计图（当月）-防火预警
			hssfSheet = wb.getSheetAt(14); // 获取第三个Sheet页
			jsonObject = new JSONObject();
			json = read1(hssfSheet, 2, 14);
			json2 = read1(hssfSheet, 16, 28);
			jsonObject.put("table_1", json);//野生动物案件数据
			jsonObject.put("table_2", json2);//森林案件数据
			result = jsonObject.toJSONString();
			appExcel = new AppExcel();
			appExcel.setId(15);
			appExcel.setJson(result);
			excelService.updateById(appExcel);
			//第十六张图
			//起火原因统计图（当月）-防火预警
			hssfSheet = wb.getSheetAt(15); // 获取第三个Sheet页
			json = read13(hssfSheet);
			result = json.toJSONString();
			appExcel = new AppExcel();
			appExcel.setId(16);
			appExcel.setJson(result);
			excelService.updateById(appExcel);
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException("导入错误");
		}
	}


	/**
	 * 刑事案件发案趋势图-森林公安
	 */
	public static JSONArray read1(HSSFSheet sheet, int startRow, int endRow) {
		int cells = 1000;//总列数
		ArrayList<ArrayList<Object>> administrativeEntities = new ArrayList<>();
		for (int i = startRow; i <= endRow; i++) {
			HSSFRow row = sheet.getRow(i);
			int j = 1;
			while (j <= cells) {
				HSSFCell cell = row.getCell(j);
				if (cell == null) {
					if (j != 0) {
						cells = j;
						break;
					}
					continue;
				}
				String value = getValue(cell);
				int number = 0;
				if (j != 0) {
					number = value.length() == 0 ? 0 : (int) Double.parseDouble(value);
				}
				ArrayList<Object> administrativeEntity = null;
				if (administrativeEntities.size() > j - 1) {
					administrativeEntity = administrativeEntities.get(j - 1);
				}
				switch (i % 14) {
					case 2://年份
						if (value.equals("")) {
							if (j != 0) {
								cells = j;
							}
							break;
						}
						administrativeEntity = new ArrayList();
						administrativeEntities.add(administrativeEntity);
						break;
					default:
						administrativeEntity.add(number);
						break;
				}
				j++;
			}
		}
		return (JSONArray) JSONArray.toJSON(administrativeEntities);
	}

	/**
	 * 森林案件统计图（当月）-森林公安
	 */
	public static JSONArray read2(HSSFSheet sheet) {
		ArrayList<Integer> numbers = new ArrayList<>();
		for (int i = 2; i < 11; i++) {
			HSSFRow row = sheet.getRow(i);
			String value = getValue(row.getCell(2));
			numbers.add(value.length() == 0 ? 0 : (int) Double.parseDouble(value));
		}
		return (JSONArray) JSONArray.toJSON(numbers);
	}

	/**
	 * 野生动物案件统计图（当月）-森林公安
	 */
	public static JSONArray read3(HSSFSheet sheet) {
		ArrayList<Integer> numbers = new ArrayList<>();
		for (int i = 2; i < 10; i++) {
			HSSFRow row = sheet.getRow(i);
			String value = getValue(row.getCell(2));
			numbers.add(value.length() == 0 ? 0 : (int) Double.parseDouble(value));
		}
		return (JSONArray) JSONArray.toJSON(numbers);
	}


	/**
	 * 刑事案人员属性图（当月）-森林公安
	 */
	public static JSONArray read4(HSSFSheet sheet) {
		ArrayList<ArrayList> numbers = new ArrayList<>();
		ArrayList<Integer> numbers1 = new ArrayList<>();//野生动物案件数量（当月）
		ArrayList<Integer> numbers2 = new ArrayList<>();//森林案件数量（当月）
		for (int i = 2; i < 8; i++) {
			HSSFRow row = sheet.getRow(i);
			String value1 = getValue(row.getCell(1));
			numbers1.add(value1.length() == 0 ? 0 : (int) Double.parseDouble(value1));
			String value2 = getValue(row.getCell(2));
			numbers2.add(value2.length() == 0 ? 0 : (int) Double.parseDouble(value2));
		}
		numbers.add(numbers1);
		numbers.add(numbers2);
		return (JSONArray) JSONArray.toJSON(numbers);
	}

	/**
	 * 行政案人员属性图（当月）-森林公安
	 */
	public static JSONArray read5(HSSFSheet sheet) {
		ArrayList<ArrayList<Integer>> numbers1 = new ArrayList<ArrayList<Integer>>();//野生动物案件数量（当月）
		for (int i = 2; i < 6; i++) {
			HSSFRow row = sheet.getRow(i);
			ArrayList<Integer> keys = new ArrayList<>();
			for (int j = 1; j < 4; j++) {
				String value1 = getValue(row.getCell(j));
				keys.add(value1.length() == 0 ? 0 : (int) Double.parseDouble(value1));
			}
			numbers1.add(keys);
		}
		return (JSONArray) JSONArray.toJSON(numbers1);
	}

	/**
	 * 案件总数统计表-森林公安
	 */
	public static JSONArray read6(HSSFSheet sheet) {
		ArrayList<ArrayList> numbers = new ArrayList<>();
		ArrayList<Integer> numbers1 = new ArrayList<>();//刑事案件数
		ArrayList<Integer> numbers2 = new ArrayList<>();//行政案件数
		for (int i = 2; i < 7; i++) {
			HSSFRow row = sheet.getRow(i);
			String value1 = getValue(row.getCell(1));
			numbers1.add(value1.length() == 0 ? 0 : (int) Double.parseDouble(value1));
			String value2 = getValue(row.getCell(2));
			numbers2.add(value2.length() == 0 ? 0 : (int) Double.parseDouble(value2));
		}
		numbers.add(numbers1);
		numbers.add(numbers2);
		return (JSONArray) JSONArray.toJSON(numbers);
	}

	/**
	 * 案件分区统计图（当月）-森林公安
	 */
	public static JSONArray read7(HSSFSheet sheet, int start, int end) {
		//首先确定行数
		HSSFRow row = sheet.getRow(start);
		int count = 0;
		for (int i = 0; i < 1000; i++) {
			HSSFCell hssfCell = row.getCell(i);
			if (hssfCell == null && i != 0) {
				count = i;
				break;
			}
			String value = getValue(hssfCell);
			if (value.length() == 0 && i != 0) {
				count = i;
				break;
			}
		}
		ArrayList<ArrayList<Integer>> numbers1 = new ArrayList<ArrayList<Integer>>();//刑事案件数
		ArrayList<Integer> list;
		for (int i = start; i < end; i++) {
			row = sheet.getRow(i);
			for (int j = 1; j < count; j++) {
				if (numbers1.size() < j) {
					numbers1.add(new ArrayList<>());
				}
				HSSFCell hssfCell = row.getCell(j);
				String value = getValue(hssfCell);
				list = numbers1.get(j - 1);
				list.add(value.length() == 0 ? 0 : (int) Double.parseDouble(value));
			}
		}

		return (JSONArray) JSONArray.toJSON(numbers1);
	}

	/**
	 * 起火原因统计图-防火预警
	 */
	public static JSONArray read8(HSSFSheet sheet) {
		ArrayList<Integer> numbers = new ArrayList<>();
		for (int i = 2; i < 8; i++) {
			HSSFRow row = sheet.getRow(i);
			String value = getValue(row.getCell(1));
			numbers.add(value.length() == 0 ? 0 : (int) Double.parseDouble(value));
		}
		return (JSONArray) JSONArray.toJSON(numbers);
	}

	/**
	 * 森林火灾属性图（当月）-防火预警
	 */
	public static JSONArray read9(HSSFSheet sheet) {
		ArrayList<Integer> numbers = new ArrayList<>();
		for (int i = 2; i < 6; i++) {
			HSSFRow row = sheet.getRow(i);
			String value = getValue(row.getCell(1));
			numbers.add(value.length() == 0 ? 0 : (int) Double.parseDouble(value));
		}
		return (JSONArray) JSONArray.toJSON(numbers);
	}

	/**
	 * 火情总数统计表-防火预警
	 */
	public static JSONArray read10(HSSFSheet sheet) {
		ArrayList<ArrayList> numbers = new ArrayList<>();
		ArrayList<Integer> numbers1 = new ArrayList<>();//刑事案件数
		ArrayList<Integer> numbers2 = new ArrayList<>();//行政案件数
		for (int i = 2; i < 6; i++) {
			HSSFRow row = sheet.getRow(i);
			String value1 = getValue(row.getCell(1));
			numbers1.add(value1.length() == 0 ? 0 : (int) Double.parseDouble(value1));
			String value2 = getValue(row.getCell(2));
			numbers2.add(value2.length() == 0 ? 0 : (int) Double.parseDouble(value2));
		}
		numbers.add(numbers1);
		numbers.add(numbers2);
		return (JSONArray) JSONArray.toJSON(numbers);
	}

	/**
	 * 防火预警信息表-防火预警
	 */
	public static JSONArray read11(HSSFSheet sheet) {
		ArrayList<ArrayList<String>> numbers = new ArrayList<ArrayList<String>>();//刑事案件数
		ArrayList<String> list = null;
		for (int i = 2; i < 7; i++) {
			HSSFRow row = sheet.getRow(i);
			list = new ArrayList<>();
			for (int j = 1; j < 6; j++) {
				HSSFCell hssfCell = row.getCell(j);
				String value = getValue(hssfCell);
				list.add(value);
			}
			numbers.add(list);
		}
		return (JSONArray) JSONArray.toJSON(numbers);
	}

	/**
	 * 火情分区统计图（当月）-防火预警
	 */
	public static JSONArray read12(HSSFSheet sheet) {
		ArrayList<ArrayList> numbers = new ArrayList<>();
		ArrayList<Integer> numbers1 = new ArrayList<>();//刑事案件数
		ArrayList<Integer> numbers2 = new ArrayList<>();//行政案件数
		for (int i = 2; i < 6; i++) {
			HSSFRow row = sheet.getRow(i);
			String value1 = getValue(row.getCell(1));
			numbers1.add(value1.length() == 0 ? 0 : (int) Double.parseDouble(value1));
			String value2 = getValue(row.getCell(2));
			numbers2.add(value2.length() == 0 ? 0 : (int) Double.parseDouble(value2));
		}
		numbers.add(numbers1);
		numbers.add(numbers2);
		return (JSONArray) JSONArray.toJSON(numbers);
	}

	/**
	 * 火灾案人员属性图（当月）-防火预警
	 */
	public static JSONArray read13(HSSFSheet sheet) {
		ArrayList<Integer> numbers = new ArrayList<>();
		for (int i = 2; i < 6; i++) {
			HSSFRow row = sheet.getRow(i);
			String value = getValue(row.getCell(1));
			numbers.add(value.length() == 0 ? 0 : (int) Double.parseDouble(value));
		}
		return (JSONArray) JSONArray.toJSON(numbers);
	}

	private static String getValue(HSSFCell hssfCell) {
		if (hssfCell.getCellType() == CellType.BOOLEAN) {
			return String.valueOf(hssfCell.getBooleanCellValue());
		} else if (hssfCell.getCellType() == CellType.NUMERIC) {
			return String.valueOf(hssfCell.getNumericCellValue());
		} else if (hssfCell.getCellType() == CellType.FORMULA) {
			return String.valueOf(hssfCell.getCellFormula());
		} else if (hssfCell.getCellType() == CellType.BLANK) {
			return String.valueOf(hssfCell.getStringCellValue());
		} else {
			return String.valueOf(hssfCell.getStringCellValue());
		}
	}
}
